package com.travel.system.mapper;

import com.travel.system.domain.EduStudent;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * 【请填写功能名称】Mapper接口
 *
 * @author xianyue
 * @date 2020-12-29
 */
public interface EduStudentMapper {
    /**
     * 查询【请填写功能名称】
     *
     * @param id 【请填写功能名称】ID
     * @return 【请填写功能名称】
     */
    public EduStudent selectEduStudentById(Integer id);

    /**
     * 查询【请填写功能名称】列表
     *
     * @param eduStudent 【请填写功能名称】
     * @return 【请填写功能名称】集合
     */
    public List<EduStudent> selectEduStudentList(EduStudent eduStudent);

    /**
     * 新增【请填写功能名称】
     *
     * @param eduStudent 【请填写功能名称】
     * @return 结果
     */
    public int insertEduStudent(EduStudent eduStudent);

    /**
     * 修改【请填写功能名称】
     *
     * @param eduStudent 【请填写功能名称】
     * @return 结果
     */
    public int updateEduStudent(EduStudent eduStudent);

    /**
     * 删除【请填写功能名称】
     *
     * @param id 【请填写功能名称】ID
     * @return 结果
     */
    public int deleteEduStudentById(Integer id);

    /**
     * 批量删除【请填写功能名称】
     *
     * @param ids 需要删除的数据ID
     * @return 结果
     */
    public int deleteEduStudentByIds(Integer[] ids);

    /**
     * 根据起始日期获取学生(未入学儿童)
     */
    @Select("select " +
            "a.*,a.town_id as townId,a.village_id as villageId,a.middle_school as middleSchool,a.young_school as youngSchool," +
            "a.edge_people as edgePeople,a.director_education as directorEducation,a.town_header as townHeader,a.village_header as villageHeader," +
            "a.disability_type as disabilityType,a.disability_level as disabilityLevel,a.can_not_study as canNotStudy,a.school_teacher as schoolTeacher," +
            "a.specia_teacher as speciaTeacher,a.special_study as specialStudy,a.class_study as classStudy,a.behind_children as behindChildren," +
            "a.accompanying_children as accompanyingChildren,a.is_late as isLate,a.in_school as inSchool,a.drop_school as dropSchool,a.study_young as studyYoung," +
            "a.study_middle as studyMiddle,a.out_city as outCity,a.out_province as outProvince,a.create_time as createTime" +
            " from `edu_student` a where birth <=#{d1} and birth >=#{d2} and graduation=0")
    public List<EduStudent> getStudentByDate(@Param("d1") Date d1, @Param("d2") Date d2);

    /**
     * 根据起始日期获取学生(未入贫困学儿童)
     */
    @Select("select " +
            "a.*,a.town_id as townId,a.village_id as villageId,a.middle_school as middleSchool,a.young_school as youngSchool," +
            "a.edge_people as edgePeople,a.director_education as directorEducation,a.town_header as townHeader,a.village_header as villageHeader," +
            "a.disability_type as disabilityType,a.disability_level as disabilityLevel,a.can_not_study as canNotStudy,a.school_teacher as schoolTeacher," +
            "a.specia_teacher as speciaTeacher,a.special_study as specialStudy,a.class_study as classStudy,a.behind_children as behindChildren," +
            "a.accompanying_children as accompanyingChildren,a.is_late as isLate,a.in_school as inSchool,a.drop_school as dropSchool,a.study_young as studyYoung," +
            "a.study_middle as studyMiddle,a.out_city as outCity,a.out_province as outProvince,a.create_time as createTime" +
            " from `edu_student` a where birth <=#{d1} and birth >=#{d2} and graduation=0 and record=1 or edge_people=1")
    public List<EduStudent> getPoorStudent(@Param("d1") Date d1, @Param("d2") Date d2);

    /**
     * 根据起始日期获取学生(未入学特殊儿童学儿童)
     */
    @Select("select " +
            "a.*,a.town_id as townId,a.village_id as villageId,a.middle_school as middleSchool,a.young_school as youngSchool," +
            "a.edge_people as edgePeople,a.director_education as directorEducation,a.town_header as townHeader,a.village_header as villageHeader," +
            "a.disability_type as disabilityType,a.disability_level as disabilityLevel,a.can_not_study as canNotStudy,a.school_teacher as schoolTeacher," +
            "a.specia_teacher as speciaTeacher,a.special_study as specialStudy,a.class_study as classStudy,a.behind_children as behindChildren," +
            "a.accompanying_children as accompanyingChildren,a.is_late as isLate,a.in_school as inSchool,a.drop_school as dropSchool,a.study_young as studyYoung," +
            "a.study_middle as studyMiddle,a.out_city as outCity,a.out_province as outProvince,a.create_time as createTime" +
            " from `edu_student` a where birth <=#{d1} and birth >=#{d2} and graduation=0 and disability=1")
    public List<EduStudent> getSpecialStudent(@Param("d1") Date d1, @Param("d2") Date d2);


    /**
     *根据日期和镇id获取学生列表
     *
     */
    @Select("select " +
            "a.*,a.town_id as townId,a.village_id as villageId,a.middle_school as middleSchool,a.young_school as youngSchool," +
            "a.edge_people as edgePeople,a.director_education as directorEducation,a.town_header as townHeader,a.village_header as villageHeader," +
            "a.disability_type as disabilityType,a.disability_level as disabilityLevel,a.can_not_study as canNotStudy,a.school_teacher as schoolTeacher," +
            "a.specia_teacher as speciaTeacher,a.special_study as specialStudy,a.class_study as classStudy,a.behind_children as behindChildren," +
            "a.accompanying_children as accompanyingChildren,a.is_late as isLate,a.in_school as inSchool,a.drop_school as dropSchool,a.study_young as studyYoung," +
            "a.study_middle as studyMiddle,a.out_city as outCity,a.out_province as outProvince,a.create_time as createTime" +
            " from `edu_student` a where birth <=#{d1} and birth >=#{d2} and town_id=#{zoneId}")
    public List<EduStudent> getStudentByDateAndZone(Date d1, Date d2,Integer zoneId);

    /**
     * 建档立卡户数统计
     */
    @Select("select count(*) from sp_student where record=1 and town_id=#{zoneId} and birth<=#{d1} and birth >= #{d2} group by address")
    public Integer statisticalTotal(Date d1, Date d2,Integer zoneId);

}
